1. Field of the Invention
The present invention is related to optimized parameter binding (e.g., for application program parameters).
2. Description of the Related Art
A database system is one in which data is stored, retrieved, and processed. Data records in a database management system (DBMS) in a computer may be maintained in tables, which are a collection of rows all having the same columns. Each column maintains information on a particular type of data for the data records which comprise the rows. Tables in the database are searched using, for example, a Structured Query Language (SQL), which specifies search operations or predicates to perform on columns of tables in the database to qualify rows in the database tables that satisfy the search conditions.
Relational DataBase Management System (RDBMS) software using a Structured Query Language (SQL) interface is well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
In some systems, an application program passes information to a database system and receives information from the database system via an interface that specifies host variables, host variable arrays, parameter markers, and/or a user descriptor area. In some systems, the interface is a SQL descriptor area (SQLDA). A SQLDA is a control block that describes the data portion of an Application Programming Interface (API) between a local application program (“local application”) and a DB2® Universal Database system (available from International Business Machines Corporation) and is used for execution of an SQL statement, such as an insert statement or a fetch statement. An insert statement provides values to be inserted into a table in the host variables. A fetch statement retrieves zero, one, or multiple rows from a table and assigns the values of the rows returned to host variables.
A host variable is a variable that is referred to by embedded SQL statements in a host (e.g., client) application program (“client application”). Host variables may be used in the application program to transmit data between tables in a database and application program work areas. A host variable array is an array in which each element of the array contains a value for the same column. For example, for an insert statement, the SQLDA provides a host variable array that holds values to be inserted into a table, and, for a fetch statement, the SQLDA provides a host variable array that is to be used to contain values from a row of a table. A parameter marker is a marker (e.g., a question mark (“?”)) that appears in a statement string of a dynamic SQL statement. The parameter marker may appear where a host variable might appear if the statement string was a static SQL statement.
For more information on SQLDA, the insert statement, the fetch statement, or host variable structures, see DB2® Universal Database for OS/390® and z/OS®, SQL Reference Version 7, October 2002; DB2® Universal Database for OS/390® and z/OS® Application Programming and SQL Guide Version 7, October 2002; and DB2® Universal Database for iSeries SQL Reference Version 5, 2002; each of which is available from International Business Machines Corporation.
For multiple row inserts and fetches, some application programs insert or fetch one row of a table at a time, which increases execution costs. Some of the execution costs of an SQL statement to insert or fetch rows are incurred due to the DBMS determining the type of data present in the database and in the application program (e.g., an application program may describe data using a different type than the database), finding out whether the database needs to convert the data from one encoding scheme (e.g., ASCII, EBCDIC, or Unicode) into another encoding scheme or from one data type (e.g., decimal or integer) to another data type, checking whether data conversion is valid (e.g., whether there is compatibility between the encoding schemes), getting information as to where to move data (i.e., the application program may change the source or target areas for each insert and fetch statement), and moving the data from the database to the application program or vice-versa. Thus, row by row inserts and fetches are expensive using existing parameter binding techniques because the DBMS cannot ensure that the application programs have not changed the data portion of their API from SQL statement to SQL statement, and, therefore, must determine information, such as data type, length, and encoding, during processing of each SQL statement. The term “parameter binding” refers to a process of validating, converting, and moving data either to or from application program input or output parameters. Also, the terms “bind-in” or “bind-out” refer to the process of checking the validity of a parameter or of host variables. The validity check includes determining whether the data types are compatible, performing length checking, performing conversions (of data type or encoding), verifying valid data (e.g., decimal data), and scanning for NUL terminators. A NUL terminator may be a single character that denotes the end of a string.
By inserting and fetching multiple rows of data at a time, the database can decrease the execution cost of the SQL statement. For example, the application program may not change the definitions of the parameters for a multiple row insert statement because the database does not return control to the application program until multiple rows are inserted or fetched. In conventional systems, row binding is performed for inserting and fetching multiple rows of data, which is inefficient. That is, each row of data is processed separately. So even though the data types likely have not changed, the DBMS has to assume the data types may be changing as that row is processed (e.g., from integer, to decimal, to character), which leads to inefficiencies in processing.
The DB2® for OS/390® V7 system reduced processing costs by providing multiple paths on second fetches for local and distributed application programs. In particular, different modules processed local and distributed application programs. Also, some more efficient modules processed requests from application programs that provide host variable definitions that match those of the database definitions for tables being processed (i.e., application programs not requiring conversions), while other (possibly less efficient) modules processed requests from application programs that required conversions (e.g., of data types). On each fetch, the module initially assigned to processing the request would determine whether the application program sending the request requires any conversions. If the module initially assigned to processing the request only processes requests from application programs that do not require conversions, and the request does require conversions, or vice versa, then the request may have to be processed by a different module, which again leads to inefficiencies. Thus, for fetch processing, the cost may be more expensive, as the user descriptor area may have changed and needs to be checked. On the other hand, when a user descriptor area is unchanged, the faster modules that would not perform data type and more extensive data validity checks could process a request on second fetches. However, this approach is for certain special case scenarios.
Therefore, there is a need in the art for an improved technique for processing row inserts and fetches.